package com.admin.dao.bookdao;

import com.admin.dto.BookListBean;
import com.admin.dto.BookLossBean;
import com.admin.dto.QueryReturnListBean;
import com.pojo.BookDamageBean;
import com.pojo.BooksBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl implements BookDaoInF {
    @Override
    public boolean InsertInfo(String sql, Connection conn, BooksBean booksBean) throws SQLException {
        boolean bool = false;
        PreparedStatement pstm = null;
        ResultSet resultSet = null;

        try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, booksBean.getBookName());
            pstm.setDouble(2, booksBean.getPrice());
            pstm.setDouble(3, booksBean.getBorrowPrice());//借阅单价
            pstm.setString(4, booksBean.getAuthor());//作者
            pstm.setString(5, booksBean.getPress());//地址
            pstm.setString(6, booksBean.getPublicDate());//出版日期
            pstm.setLong(7, booksBean.getStock());

            pstm.setLong(8, booksBean.getType());//书籍的类型
            pstm.setString(9, booksBean.getIsLower());//是否下架
            pstm.setString(10, booksBean.getBookNumber());
            pstm.setString(11, booksBean.getAddress());
            pstm.setString(12, booksBean.getPicture());
            pstm.setLong(13, booksBean.getOriginalStock());


            int len = pstm.executeUpdate();
//            System.out.println("插入结果" + len);
            if (len > 0) {
                bool = true;
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        }
        return bool;

    }

    @Override
    public List<QueryReturnListBean> getAllBookInfo(String sql, Connection conn, String idCard) throws SQLException {
        List<QueryReturnListBean> list = new ArrayList<>();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        QueryReturnListBean borObj = new QueryReturnListBean();
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, idCard);
            rs = pstm.executeQuery();
            while (rs.next()) {
                borObj.setBookId(rs.getString("bid"));
                borObj.setBookName(rs.getString("bookname"));
                borObj.setBorDate(rs.getString("bordate"));
                borObj.setRepayDate(rs.getString("repaydate"));
                borObj.setPress(rs.getString("press"));
                borObj.setAddress(rs.getString("address"));
                list.add(borObj);
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return list;
    }

    /**
     * 直接查询所有书籍信息
     *
     * @param sql
     * @param conn
     * @return
     * @throws SQLException
     */
    @Override
    public List<BookListBean> queryAllBookInfo(String sql, Connection conn) throws SQLException {
        List<BookListBean> list = new ArrayList<>();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        BookListBean borObj = new BookListBean();
//        String sql = "select bookid,bookname,author,press,typename,stock,borrowprice from books,booktype where type=typeid";

        try {
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                borObj.setBookId(rs.getLong("bookid"));
                borObj.setBookName(rs.getString("bookname"));
                borObj.setAuthor(rs.getString("author"));
                borObj.setTypeName(rs.getString("typename"));
                borObj.setStock(rs.getLong("stock"));
                borObj.setPress(rs.getString("press"));
                borObj.setPrice(rs.getLong("borrowprice"));
                list.add(borObj);
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return list;
    }

    @Override
    public List<BookLossBean> queryBookDemageInfo(String sql, Connection conn) throws SQLException {
        List<BookLossBean> list = new ArrayList<>();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        BookLossBean borObj = new BookLossBean();

        try {
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                borObj.setId(rs.getInt("id"));
                borObj.setIdcard(rs.getString("idcard"));
                borObj.setBookName(rs.getString("bname"));
                borObj.setPrice(rs.getDouble("pr"));
                borObj.setBookNumber(rs.getString("bnum"));
                borObj.setDamagedegree(rs.getString("amge"));
                borObj.setBookDamageMsg(rs.getString("bmsg"));
                borObj.setSendTime(rs.getString("st"));
                borObj.setBorName(rs.getString("borname"));
                list.add(borObj);
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return list;
    }

    /**
     * 通过书id查询书籍信息
     *
     * @param sql
     * @param conn
     * @param bookId
     * @return
     */
    @Override
    public BooksBean queryBookById(String sql, Connection conn, String bookId) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        BooksBean borObj = new BooksBean();


        try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, bookId);
            rs = pstm.executeQuery();
            if (rs.next()) {
                borObj.setBookId(rs.getLong("bookid"));
                borObj.setBookName(rs.getString("bookname"));
                borObj.setAuthor(rs.getString("author"));
                borObj.setAddress(rs.getString("address"));
                borObj.setStock(rs.getLong("stock"));
                borObj.setPress(rs.getString("press"));
                borObj.setBorrowPrice(rs.getLong("borrowprice"));
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return borObj;
    }


    @Override
    public BooksBean queryBookByNameDao(String sql, Connection conn, String bookName) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        BooksBean borObj = new BooksBean();


        try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, bookName);
            rs = pstm.executeQuery();
            if (rs.next()) {
                borObj.setBookId(rs.getLong("bookid"));
                borObj.setBookName(rs.getString("bookname"));
                borObj.setAuthor(rs.getString("author"));
                borObj.setAddress(rs.getString("address"));
                borObj.setStock(rs.getLong("stock"));
                borObj.setPress(rs.getString("press"));
                borObj.setBorrowPrice(rs.getLong("borrowprice"));
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return borObj;
    }


    @Override
    public boolean updateStock(String sql, Connection conn, String bookId) throws SQLException {
        boolean bool = false;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, bookId);
            int i = pstm.executeUpdate();
            if (i > 0) {
                bool = true;
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return bool;
    }

    @Override
    public String demageDegreeDao(String sql, Connection conn, Integer id) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        BookDamageBean bookDamageBean = new BookDamageBean();
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, id);
            rs = pstm.executeQuery();

            while (rs.next()) {
                bookDamageBean.setDamagedegree(rs.getString("damagedegree"));


            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return bookDamageBean.getDamagedegree();
    }


    @Override
    public List<BookListBean> getbookList(String sql, Connection conn, int pageIndex, int pageSize) throws SQLException {

        List<BookListBean> list = new ArrayList<>();
        PreparedStatement pstm = null;
        ResultSet resultSet = null;
        BookListBean alb = null;
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setLong(1, pageIndex);
            pstm.setLong(2, pageSize);


            resultSet = pstm.executeQuery();
            while (resultSet.next()) {
                alb = new BookListBean();
                alb.setBookId(resultSet.getLong("bookid"));
                alb.setBookName(resultSet.getString("bookname"));
                alb.setAuthor(resultSet.getString("author"));
                alb.setPrice(resultSet.getDouble("price"));
                alb.setBorrowPrice(resultSet.getDouble("borrowprice"));
                alb.setPress(resultSet.getString("press"));
                alb.setAddress(resultSet.getString("address"));
                alb.setBookNumber(resultSet.getString("booknumber"));
                alb.setStock(resultSet.getLong("stock"));
                alb.setOriginalStock(resultSet.getLong("Originalstock"));
                alb.setPublicDate(resultSet.getString("publicdate"));
                alb.setTypeName(resultSet.getString("typename"));
                list.add(alb);
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        }
        return list;


    }

    @Override
    public int getBookSum(String sql, Connection conn) throws SQLException {
        ResultSet rs = null;
        int totalrow = 0;
        PreparedStatement pstm = null;
        pstm = conn.prepareStatement(sql);
        rs = pstm.executeQuery();
        while (rs.next()) {


            totalrow = rs.getInt("count");

        }
        return totalrow;
    }

    public boolean delBookInfoById(String sql, Connection conn, int bookid) throws SQLException {
        PreparedStatement pstm = null;
        boolean bool = false;

        try {
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, bookid);
            int len = pstm.executeUpdate();
            if (len > 0) {
                bool = true;
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
        }
        return bool;
    }

    public boolean alterBookInfoById(String sql, Connection conn, BooksBean booksBean) throws SQLException {
        PreparedStatement pstm = null;
        boolean bool = false;
//UPDATE  books set booknumber=?,bookname=?,press=?,price=?,borrowprice=?,author=?
//,type=?,stock=?
//where bookid=2
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, booksBean.getBookNumber());
            pstm.setString(2, booksBean.getBookName());
            pstm.setString(3, booksBean.getPress());
            pstm.setDouble(4, booksBean.getPrice());
            pstm.setDouble(5, booksBean.getBorrowPrice());
            pstm.setString(6, booksBean.getAuthor());
            pstm.setLong(7, booksBean.getType());
            pstm.setLong(8, booksBean.getStock());

            pstm.setLong(9, booksBean.getBookId());
            int len = pstm.executeUpdate();
            if (len > 0) {
                bool = true;
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
        }
        return bool;
    }

}
